package whf.framework.tools.db;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import org.jdom.Element;
import org.jdom.output.XMLOutputter;

import whf.framework.config.Configuration;
import whf.framework.config.PropertiesConfiguration;
import whf.framework.util.StringUtils;
import whf.framework.util.Utils;

/**
 * @author king
 *
 */
public class ExportImport {
	Configuration config = null;
	private String getString(String key) throws Exception {
		if(config == null) {
			config = new PropertiesConfiguration(ExportImport.class.getResource("export.properties"));
		}
		return config.getString(key);
	}
	
	private Connection getConnection() throws Exception {
		Class.forName(getString("jdbc.driverClassName"));
		return DriverManager.getConnection(getString("jdbc.url"), getString("jdbc.username"), getString("jdbc.password"));
	}
	
	public void exportTable(String exportDir, String tableName, List<String> columns, List<String> types) throws Exception {
		assert tableName != null: "Table can not be null";
		assert columns != null && columns.size() > 0: "columns can not be empty!";
		assert types != null && types.size() == columns.size() : "types must match columns!";
		StringBuilder sb = new StringBuilder("select " + columns.get(0));
		for(int i=1; i< columns.size(); i ++) {
			sb.append(",").append(columns.get(i));
		}
		sb.append(" from ").append(tableName);
		//
		Element root = new Element(tableName);
		//add columns information
		Element columnsElement = new Element("columns");
		for(int i=0; i< columns.size(); i++){ 
			Element col = new Element("column");
			col.setAttribute("name", columns.get(i));
			col.setAttribute("type", types.get(i));
			columnsElement.addContent(col);
		}
		root.addContent(columnsElement);
		//
		Connection con = this.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try{
			stmt = con.createStatement();
			rs = stmt.executeQuery(sb.toString());
			int rowCount = 0;
			while(rs.next()) {
				Element row = new Element("row");
				row.setAttribute("rowCount", String.valueOf(rowCount));
				for(int i=0; i< columns.size(); i++){
					String value = rs.getString(columns.get(i));
					Element col = new Element("col");
					col.setAttribute("name", columns.get(i));
					if(value != null)
						col.setText(value);
					row.addContent(col);
				}
				root.addContent(row);
				rowCount ++;
			}
		} finally {
			if(rs != null) rs.close();
			if(stmt != null) stmt.close();
			if(con != null) con.close();
		}
		//
		File file = new File(exportDir + "\\" + tableName + ".xml");
		if(file.exists()) {
			file.delete();
		}
		FileOutputStream fos = new FileOutputStream(file);
		try{
			XMLOutputter outputer = new XMLOutputter();
			outputer.output(root, fos);
		} finally {
			fos.close();
		}
	}
	
	public void export2Xml() throws Exception {
		String exportDir = this.getString("export.dir");
		List<String> tables = config.getIncList("table");
		for(String tableName: tables) {
			System.out.println("Exporting " + tableName + " ...... ");
			long start = System.currentTimeMillis();
			try{
				List<String> columnsInfo = config.getIncList(tableName + ".column");
				List<String> columns = Utils.newArrayList();
				List<String> types = Utils.newArrayList();
				for(String ci: columnsInfo) {
					String[] tmp = StringUtils.split(ci, ":");
					columns.add(tmp[0]);
					types.add(tmp[1]);
				}
				this.exportTable(exportDir, tableName, columns, types);
			}catch(Exception e){
				e.printStackTrace();
			}
			long finish = System.currentTimeMillis();
			System.out.println((finish - start) + " millis ... \n");
		}
	}
	
	public void import2Table(String xmlFile, String tableName, List<String> columns, List<String> types) {
		
	}
	
	public void import2DB() throws Exception {
		String exportDir = this.getString("export.dir");
		List<String> tables = config.getIncList("table");
		for(String tableName: tables) {
			System.out.println("Exporting " + tableName + " ...... ");
			long start = System.currentTimeMillis();
			try{
				List<String> columnsInfo = config.getIncList(tableName + ".column");
				List<String> columns = Utils.newArrayList();
				List<String> types = Utils.newArrayList();
				for(String ci: columnsInfo) {
					String[] tmp = StringUtils.split(ci, ":");
					columns.add(tmp[0]);
					types.add(tmp[1]);
				}
				this.import2Table(exportDir, tableName, columns, types);
			}catch(Exception e){
				e.printStackTrace();
			}
			long finish = System.currentTimeMillis();
			System.out.println((finish - start) + " millis ... \n");
		}
	}
	
	public static void main(String[] args) throws Exception {
		ExportImport e = new ExportImport();
		e.export2Xml();
	}
}
